package dao.impl;

import dao.ScoreDao;
import pojo.Scores;
import java.util.List;

public class ScoreDaoImpl extends BaseDao implements ScoreDao {

    @Override
    public List<Scores> queryScoreByCourseId(String courseId) {
        String sql = "SELECT id,studentId,courseId,grade FROM scores where courseId=?";
       // String sql ="SELECT studentId,students.Name,courses.Name,grade FROM scores,students,courses WHERE courseId=? AND students.id=scores.studentId AND scores.courseId = courses.id";
        return queryForList(Scores.class,sql,courseId);
    }

    @Override
    public int updateGradeByStudentIdAndCourseId(Scores score) {
        String sql = "UPDATE scores SET grade=? WHERE studentId=? AND courseId=?";
        return update(sql,score.getGrade(),score.getStudentId(),score.getCourseId());
    }

    @Override
    public List<Scores> queryScoreByStudentId(String studentId) {
        String sql = "SELECT id,studentId,courseId,grade FROM scores WHERE studentId=?";
        return queryForList(Scores.class,sql,studentId);
    }

    @Override
    public Scores queryScoreByStudentIdAndCourseId(String studentId, String courseId) {
        String sql = "SELECT studentId,courseId,grade FROM scores WHERE studentId=? AND courseId=?";
        return queryForOne(Scores.class,studentId,courseId);
    }

    @Override
    public int saveScore(Scores score) {
        String sql = "INSERT INTO scores(studentId,courseId,grade) VALUES(?,?,?)";
        return update(sql,score.getStudentId(),score.getCourseId(),score.getGrade());
    }

    @Override
    public List<Scores> queryAllScore() {
        String sql = "SELECT id,studentId,courseId,grade FROM scores";
        return queryForList(Scores.class,sql);
    }

    @Override
    public Double queryStudentScoreAVGByStudentId(String studentId) {
        String sql ="SELECT AVG(grade) FROM scores WHERE studentId=?";
        return (Double) queryForSingleValue(sql,studentId);
    }
}
